use SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdOpportunityDetails')
	BEGIN
		DROP  Procedure  dbo.InsUpdOpportunityDetails
	END
GO

CREATE Procedure [dbo].[InsUpdOpportunityDetails] (
	@OPPORTUNITYID				VARCHAR(10)		= '0'
,	@OPPORTUNITYGUID			VARCHAR(100)	
,	@DESCRIPTION				VARCHAR(200)	= ''
,	@OPPTYPEID					VARCHAR(10)		= ''
,	@OPPREVENUE					VARCHAR(20)		= ''
,	@PROBABILITY				VARCHAR(10)		= ''
,	@TOTALREVENUE				VARCHAR(20)		= ''
,	@TOTALQUANTITY				VARCHAR(10)		= ''
,	@CLOSEDATE					VARCHAR(15)		= ''
,	@MILESTONEDATE				VARCHAR(15)		= ''
,	@STATUSID					VARCHAR(10)		= ''
,	@APPLICATION				VARCHAR(50)		= ''
,	@OPPSTAGEID					VARCHAR(10)		= ''
,	@COMPETITOR					VARCHAR(50)		= ''
,	@RESELLERNO					VARCHAR(10)		= ''
,	@OPPREFERRALID				VARCHAR(10)		= ''
,	@NOTEID						VARCHAR(10)		= ''
,	@NOTES						VARCHAR(max)	= ''
,	@USERID						VARCHAR(10)		= ''
-- BELOW FIELDS GOES INTO TR_RELATIONSHIP TABLE
,	@ACCOUNTNO					VARCHAR(10)	 = '0'
,	@OWNERID					VARCHAR(10)	 = '1'
,	@PRIMARYCONTACTID			VARCHAR(10)	 = ''
)
AS
BEGIN

	-- ***********************************************
	-- BELOW PARAMETERS ARE CURRENTLY HARDCODED
	-- START: DONOT MODIFY THE BELOW CODE
	-- ***********************************************
	DECLARE
		@NOTETYPE				VARCHAR(10)

	SELECT
		@NOTETYPE				= '4'
	-- ***********************************************
	-- END: DONOT MODIFY THE BELOW CODE
	-- ***********************************************

	BEGIN TRAN
		BEGIN TRY
			IF EXISTS(SELECT 1 FROM TR_OPPORTUNITYH WHERE OppGUID = @OPPORTUNITYGUID)
			BEGIN		-- UPDATE
				UPDATE TR_OPPORTUNITYH
				SET	[Description]				= @DESCRIPTION	
				,	OppType						= @OPPTYPEID		
				,	OppRevenue					= @OPPREVENUE		
				,	Probability					= @PROBABILITY	
				,	TotalRevenue				= @TOTALREVENUE	
				,	TotalQuantity				= @TOTALQUANTITY	
				,	CloseDate					= NULLIF(@CLOSEDATE,'')
				,	MileStoneDate				= NULLIF(@MILESTONEDATE,'')
				,	[Status]					= @STATUSID		
				,	[Application]				= @APPLICATION	
				,	OppStage					= @OPPSTAGEID		
				,	Competitor					= @COMPETITOR		
				,	DealStatus					= @STATUSID		
				,	ResellerNo					= @RESELLERNO		
				,	OppReferral					= @OPPREFERRALID	
				WHERE
					OppGuid						= @OPPORTUNITYGUID
			END
			ELSE
			BEGIN		-- INSERT
				SELECT @OPPORTUNITYID		= ISNULL(MAX(OPPORTUNITYID), 0) +1 FROM TR_OPPORTUNITYH

				INSERT INTO TR_OPPORTUNITYH (OpportunityId,[Description],OppType,OppRevenue,Probability,TotalRevenue,
											TotalQuantity,CloseDate,MileStoneDate,[Status],[Application],OppStage,
											Competitor,DealStatus,ResellerNo,OppReferral,OppGuid)
				VALUES(
					@OPPORTUNITYID				--	OpportunityId
				,	@DESCRIPTION				--	Description
				,	@OPPTYPEID					--	OppType
				,	@OPPREVENUE					--	OppRevenue
				,	@PROBABILITY				--	Probability
				,	@TOTALREVENUE				--	TotalRevenue
				,	@TOTALQUANTITY				--	TotalQuantity
				,	NULLIF(@CLOSEDATE,'')		--	CloseDate
				,	NULLIF(@MILESTONEDATE,'')	--	MileStoneDate
				,	@STATUSID					--	Status
				,	@APPLICATION				--	Application
				,	@OPPSTAGEID					--	OppStage
				,	@COMPETITOR					--	Competitor
				,	@STATUSID					--	DealStatus
				,	@RESELLERNO					--	ResellerNo
				,	@OPPREFERRALID				--	OppReferral
				,	@OPPORTUNITYGUID			--	OppGuid
				)
			END

			exec InsUpdNotes
				@NOTEID				= @NOTEID
			,	@NOTESOURCEGUID		= @OPPORTUNITYGUID
			,	@SOURCEID			= @OPPORTUNITYID
			,	@NOTE				= @NOTES
			,	@USERID				= @USERID
			,	@NOTETYPE			= @NOTETYPE


			IF ( CAST(ISNULL(@ACCOUNTNO,'0') AS INT) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @OPPORTUNITYGUID
				,	@PARTNERID	= @ACCOUNTNO
				,	@RELATIONSHIPTYPE = 11 -- OPPORTUNITY PARTNER
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END

			IF ( CAST(ISNULL(@OWNERID,'0') AS INT) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @OPPORTUNITYGUID
				,	@PARTNERID	= @OWNERID
				,	@RELATIONSHIPTYPE = 3 
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END

			IF ( CAST(ISNULL(@PRIMARYCONTACTID,'0') AS INT ) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @OPPORTUNITYGUID
				,	@PARTNERID	= @PRIMARYCONTACTID
				,	@RELATIONSHIPTYPE = 2 
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END

			COMMIT TRAN

			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>S</RESULT>
						<MESSAGE>Opportunity Details saved successfully</MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>E</RESULT>
						<MESSAGE><![CDATA[Unable to save Opportunity Details. '+ERROR_MESSAGE()+']]></MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END CATCH
END
GO
